﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ResultsManagementSystem
{
    public class StudentSubjectDao
    {
        private StudentSubject studentSubject;
        private SqlCeConnection conn;
        private SqlCeCommand cmd;
        private SqlCeDataReader rdr;

        public StudentSubjectDao()
        {
            studentSubject = new StudentSubject();

            conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        ~StudentSubjectDao()
        {
            conn.Close();
        }

        //查询某一个学生的选课状况
        public List<StudentSubject> querryStudentSubjectByStudentId(int studentId)
        {
            List<StudentSubject> studentSubjectList = new List<StudentSubject>();

            cmd.CommandText = "SELECT * FROM StudentSubject where studentId = '" + studentId + "'";

            rdr = cmd.ExecuteReader();

            while(rdr.Read())
            {
                studentSubjectList.Add(new StudentSubject(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetInt32(2)));
            }

            return studentSubjectList;

        }

        //查询某一个选课状况
        public StudentSubject querryStudentSubjectByStudentIdAndSubjectId(int studentId,int subjectId)
        {
            StudentSubject studentSubject = new StudentSubject();

            cmd.CommandText = "SELECT * FROM StudentSubject where studentId = '" + studentId + "' and subjectId = '" + subjectId + "' ";

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                studentSubject.setId(rdr.GetInt32(0));
                studentSubject.setStudentId(rdr.GetInt32(1));
                studentSubject.setSubjectId(rdr.GetInt32(2));
            }
            else
            {
                studentSubject = null;
            }

            return studentSubject;

        }

        public bool insertStudentSubject(int studentId, int subjectId)
        {
            if (querryStudentSubjectByStudentIdAndSubjectId(studentId, subjectId) == null)
            {
                //插入数据
                cmd.CommandText = "insert into StudentSubject(studentId, subjectId) values('" +studentId + "','" + subjectId + "')";
                cmd.ExecuteNonQuery();

                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
